eval 'exec perl -wS $0 ${1+"$@"}'
 if 0;

use strict;
use warnings;
use POSIX qw(strftime);

use DBI();
use File::Basename;
use File::stat;
use Data::Dumper 'Dumper';

# Following variable is used as a general file descriptor for all the 
# output of the current tool (the log file). Actually, this file will
# contain the copy of both the stdout and stderr channel.
my $G_LOG;
my $tmn_pt = "/data/samba_share/tct-huizhou/tmn_pt.txt";

################################################################################
#
################################################################################
sub get_time
{
    my $separator=shift @_;
    my @timelist = @_;
    
    my $now_time="";
    
    if (! defined $separator)
    {
	$separator=":";
    }
    
    if (scalar @timelist<1)
    {
	@timelist=localtime();
    }
    
    $now_time = strftime("%H".$separator."%M".$separator."%S",@timelist);
    
    return $now_time;
}

################################################################################
#
################################################################################
sub get_date
{
    my $separator=shift @_;
    my @timelist = @_;
    
    my $now_date="";
    
    if (! defined $separator)
    {
	$separator="/";
    }
    
    if (scalar @timelist<1)
    {
	@timelist=localtime();
    }
    
    $now_date = POSIX::strftime("%Y".$separator."%m".$separator."%d",@timelist);
    
    return $now_date;
}

################################################################################
#
################################################################################
sub mylog
{
    if ($G_LOG)
    {
	print $G_LOG get_date()." ".get_time()." ".join("",@_);
    }
}

################################################################################
#
################################################################################
sub error
{
    print STDERR get_date()." ".get_time()." [ERROR] ",@_;
    mylog "[ERROR] ", @_;
}

################################################################################
#
################################################################################
sub warning
{
    print STDERR get_date()." ".get_time()." [WARNING] ",@_;
    mylog("[WARNING],",@_);
}

################################################################################
#
################################################################################
sub debug
{
    my $h_ref_param=shift @_;
    
    if (exists $h_ref_param->{DEBUG})
    {
	print STDOUT get_date()." ".get_time()." [DEBUG] ",@_;
	mylog("[DEBUG] ",@_);
    }
}

################################################################################
#
################################################################################
sub info
{
    print STDOUT get_date()." ".get_time()." [INFO] ",@_;
    mylog("[INFO] ",@_);
}


################################################################################
#
################################################################################
sub print_h_content
{
    my $h_ref_param=shift @_;
    
    debug $h_ref_param, "Printing Global Hash Content:\n";
    debug $h_ref_param, "######################################################\n";
    foreach my $thekey (keys %$h_ref_param)
    {
	debug $h_ref_param, $thekey." => ".$h_ref_param->{$thekey}."\n";
    }
    debug $h_ref_param, "######################################################\n";
}

################################################################################
#
################################################################################
sub db_connect
{
    my $h_ref_param=shift @_;

    my $dbh = DBI->connect("DBI:mysql:database=".$h_ref_param->{DBNAME}.";host=localhost",
    #my $dbh = DBI->connect("DBI:mysql:mysql_socket=/local/tools/repository/mysql-5.0.51b/tmp/mysql.sock;database=".$h_ref_param->{DBNAME}.";host=localhost",
                         $h_ref_param->{DBLOGIN}, $h_ref_param->{DBPASSWD},
                         {'RaiseError' => 0, 'AutoCommit' => 0});

    $h_ref_param->{DB}=$dbh;

    return (defined $dbh);
}

################################################################################
#
################################################################################
sub db_disconnect
{
    my $h_ref_param=shift @_;
    $h_ref_param->{DB}->disconnect();
}

################################################################################
#
################################################################################
sub db_file_already_processed
{
    my $h_ref_param=shift @_;
    my $num_opl = shift @_;
    my $res=0;
    
    my $sth = $h_ref_param->{DB}->prepare("SELECT COUNT(*) FROM opl WHERE num_opl='$num_opl';");
    $sth->execute();
    my $ref = $sth->fetchrow_hashref();

    $sth->finish();

    return $ref->{'COUNT(*)'}!=0;
}

################################################################################
#
################################################################################
sub db_imei_already_processed
{
    my $h_ref_param=shift @_;
    my $loplcontent = shift @_;
    my $res=0;
    my $counter=0;
    my @fields=();
    my $ref; 
    my $sth;
    my $imei="";

    foreach my $line (@$loplcontent) 
    {
	@fields = split(/;/,$line);
	$imei=$fields[0];
	#print "imei: ".$imei;

	$sth = $h_ref_param->{DB}->prepare("SELECT count(*) FROM imei WHERE imei='$imei';");
	$sth->execute();
	$ref = $sth->fetchrow_hashref();
	$sth->finish();

	#print $ref->{'count(*)'}."\n";
	if ($ref->{'count(*)'} != 0)
	{
	    error("imei $imei duplicated.\n");
	    $counter++;
	}
	
    }

    if ($counter > 0)
    {
	return 0;
    }
    
    return 1;
}

################################################################################
#
################################################################################
sub get_formatted_date
{
    my $h_ref_param=shift @_;
    my $raw_date = shift @_;

    my $formatted_date = "";
    my $year = "";
    my $month = "";
    my $day = "";
    my $hour = "";
    my $minute = "";
    my $second = "";
    
    $year = substr($$raw_date,0,4);
    $month = substr($$raw_date,4,2);
    $day = substr($$raw_date,6,2);
    $hour = substr($$raw_date,8,2);
    $minute = substr($$raw_date,10,2);
    $second = substr($$raw_date,12,2);
    
    $formatted_date = $year. "-" .$month. "-" .$day. " " .$hour. ":" . $minute. ":" .$second;
    return $formatted_date;
}

################################################################################
#
################################################################################
sub db_data_insert
{
    my $h_ref_param=shift @_;
    my $lref_data = shift @_;

    #warn Dumper [@$lref_data];

    my $file_create_date = shift @_;
    my $client_id = shift @_;
    my $com_ref_id = shift @_;
    my $file_name = shift @_;
    my $insert_date = "";
    
    $insert_date=get_date("-")." ".get_time(":");
    my $res=0;
    my $sth;
    my $num_opl = substr(@$lref_data[7],-8,10);
    my $date_pack = get_formatted_date($h_ref_param,\@$lref_data[18]);
    my $date_deliv = get_formatted_date($h_ref_param,\@$lref_data[19]);

    #print "com_ref_id: " .@$lref_data[4]. "\n";
    
    # trap errors using eval{}
    eval {
	$sth = $h_ref_param->{DB}->prepare("INSERT INTO opl (ref_com,insert_date,file_date,com_ref_id,ref_com_cli,ref_indus_er,num_opl,nb_imei_opl,client_id,client_order_number,alc_order_number,date_pack,date_deliv,city_deliv,ref_color,fam_prod,code_prod,des_ref_com,ean13,center_id,file_name,status) VALUES ('@$lref_data[4] ', '$insert_date', '$file_create_date','$com_ref_id','@$lref_data[5] ','@$lref_data[6] ','$num_opl','@$lref_data[8]','$client_id','@$lref_data[10] ','@$lref_data[11] ','$date_pack ','$date_deliv ','@$lref_data[20] ','@$lref_data[23] ','@$lref_data[24] ',' @$lref_data[25]',' @$lref_data[26]','@$lref_data[27] ','$h_ref_param->{CENTER}','$file_name',1);");
	
	$res=$sth->execute();
	$h_ref_param->{DB}->commit();
    };
    
    if ( $@ )
    {
	error("Transaction aborted: $@\n");
	$h_ref_param->{DB}->rollback();
	return 0;
    }
    
    return $res;
}

################################################################################
#
################################################################################
sub db_get_id
{
    my $h_ref_param=shift @_;
    my $select_count = shift @_;
    my $select = shift @_;
    my $res=0;
    my $sth;

    #print "SELECT_COUNT: " .$select_count. "\n";
    #print "SELECT: " .$select. "\n";

    $sth = $h_ref_param->{DB}->prepare("$select_count");
    $sth->execute();
    my $ref = $sth->fetchrow_hashref();
    
    $sth->finish();
    
    if ($ref->{'count(*)'} != 0)
    {
	$sth = $h_ref_param->{DB}->prepare("$select");
	$sth->execute();
	my $ref = $sth->fetchrow_hashref();
	
	$sth->finish();
	return $ref->{'id'};
    }

    return $ref->{'count(*)'}!=0;
}

################################################################################
#
################################################################################
sub db_imei_insert
{
    my $h_ref_param=shift @_;
    my $imei = shift @_;
    my $event_id = shift @_;
    my $res=0;
    my $sth;
    my $imei_additional_info_id=shift @_;

    #@lopldata = split(/;/, $opldata);

    #foreach my $rec (@$lref_opl_content)
    #{
	#print $rec. "\n";
    #}
    #return 1;
    
    # trap errors using eval{}
    eval {
	#foreach my $myline (@$lref_opl_content)
	#{
	    $sth = $h_ref_param->{DB}->prepare("INSERT INTO imei (imei,event_id,imei_additional_info_id) VALUES ( '$imei', '$event_id','$imei_additional_info_id');");
	    
	    $res=$sth->execute();
	#}
    };
    
    if ( $@ )
    {
	error("Transaction aborted: $@\n");
	$h_ref_param->{DB}->rollback();
	return 0;
    }

    return $res;
    #return 1;
}

################################################################################
#
################################################################################
sub db_log_insert
{
    my $h_ref_param=shift @_;
    my $opl_id = shift @_;
    my $res=0;
    my $sth;

    # trap errors using eval{}
    eval {
	if ($h_ref_param->{OPL_TYPE} eq "S") {
	    $sth = $h_ref_param->{DB}->prepare("INSERT INTO log (opl_id,file_type,status_id) VALUES ('$opl_id','simlock_opl',1), ('$opl_id','imei_opl',1);");
	}
	else {
	    $sth = $h_ref_param->{DB}->prepare("INSERT INTO log (opl_id,file_type,status_id) VALUES ('$opl_id','imei_opl',1);");
	}
	$res=$sth->execute();
    };
    
    if ( $@ )
    {
	error("Transaction aborted: $@\n");
	$h_ref_param->{DB}->rollback();
	return 0;
    }

    return $res;
    #return 1;
}

################################################################################
#
################################################################################
sub db_add_event
{
    my $h_ref_param=shift @_;
    my $opl_id = shift @_;
    my $res=0;
    my $sth;
    my $insert_date = "";
    $insert_date=get_date("-")." ".get_time(":");

    # trap errors using eval{}
    eval {
	    $sth = $h_ref_param->{DB}->prepare("INSERT INTO event (event_type_id,date,data) VALUES (2,'$insert_date','$opl_id');");
	    $res=$sth->execute();
    };
    
    if ( $@ )
    {
	error("Transaction aborted: $@\n");
	$h_ref_param->{DB}->rollback();
	return 0;
    }

    return $res;
    #return 1;
}

################################################################################
#
################################################################################
sub db_add_client_code
{
    my $h_ref_param=shift @_;
    my $client_code = shift @_;
    my $res=0;
    my $sth;
    
    # trap errors using eval{}
    eval {
	    $sth = $h_ref_param->{DB}->prepare("INSERT INTO client (email,code_client,code,country_id,company_id) VALUES ('N/A','$client_code','N/A',1,1);");
	    $res=$sth->execute();
    };
    
    if ( $@ )
    {
	error("Transaction aborted: $@\n");
	$h_ref_param->{DB}->rollback();
	return 0;
    }

    return $res;
    #return 1;
}

################################################################################
#
################################################################################
sub get_content
{
    my $h_ref_param=shift @_;
    my $file = shift @_;
    my $line = "";
    my $cmd = "";
    my $length = "";
    my $lref_content = shift @_;
    my $ref_data = shift @_;
    my $opl_file = shift @_;
    my $field = "";
    my @fields = ();
    my $block1 = "";
    my $block2 = "";
    my $block3 = "";
    my $block4 = "";
    my $block5 = "";
    my $block6 = "";
    my $block7 = "";
    my $block8 = "";
    my $block9 = "";
    my @comRefList="";
    my $comRef="";
    my $counter=0;
    
    @$lref_content = ();
    $$ref_data = ();
    @$opl_file = ();
    
    open(FILE, "<$file");
    @$lref_content = <FILE>;

    foreach $line (@$lref_content)
    {
	$counter++;
	$line=~s/\r\n$//;
	$line=~s/\n\r$//;
	$line=~s/\n$//;
	$line=~s/\r$//;
	
	chomp($line);
	
	$length = length($line);
	#debug $h_ref_param, "File current " .$file. " length = " .$length. "\n";
	if (($length != 495) && ($length != 511) && ($length != 527))
	{
	    debug $h_ref_param, "File current " .$file. " length = " .$length. "\n";
	    return 0;
	} 
	
	# check if field opl_number is numeric
	#@fields = split (';',$line);
	
	#$line =~/^(.{15};.{3};.{3};.{20};.{18};.{20};.{18};)(.{10})(;.+)/;
	$line =~/^(.{15};.{3};.{3};.{20};.{18};.{20};.{18};)(.{10})(;.{6};.{13};.{10};.{10};)(.{15})(;.{15};.{6};)(.{15})(;.+)/;
	
	$block1 = $1;
	$block2 = $2;
	$block3 = $3;
	$block4 = $4;
	$block5 = $5;
	$block6 = $6;
	$block7 = $7;
	$block8 = "               ;";
	$block9 = "               ;               ;";

	$block2 =~s/[a-zA-Z]/0/g;

	$comRef=$block1;
	$comRef=~/(.{15};.{3};.{3};.{20};)(.{18})(;.{20};.{18};)/;
	$comRef=$2;
	$comRef=~s/\ //g;

        # manage Orange ASN file.
	# as the same "ship to code" is used for both AVT and ASN format
        # this code is replaced by the value "0000099999" when the commercial reference "S211X-2[X=C,D]RGFR1 is processed,
        # so that multisimgen can select the good template
	if ($comRef=~/^S211X-2.RGFR/)
	{
	    $block3=~/^(;\d{6};)(.{13})(;.+)/;
	    $block3=$1."0000000099999".$3;
	}

	        # manage Orange Spain ASN file.
	# as the same "ship to code" is used for all format
        # this code is replaced by the value "0000088888" when the commercial reference "S211X-2*RGES1" is processed,
        # so that multisimgen can select the good template
	if ($comRef=~/^S211X-2.RGES/)
	{
	    $block3=~/^(;\d{6};)(.{13})(;.+)/;
	    $block3=$1."0000000088888".$3;
	}

        # only for test.
        # remove when done.
	#if ($comRef=~/^E221X-2.RGFR1/)
	#{
	    #$block3=~/^(;\d{6};)(.{13})(;.+)/;
	    #$block3=$1."0000000099999".$3;
	    #$block2="0000099999";
	#}

	$block4 =~s/[a-zA-Z]/0/g;
	$block6 =~s/[a-zA-Z]/0/g;

	if ($length == 495)
	{
	    push (@$opl_file,$block1.$block2.$block3.$block4.$block5.$block6.$block7.$block9);
	    #debug $h_ref_param, "2 fields added.\n";
	}
	if ($length == 511)
	{
	    push (@$opl_file,$block1.$block2.$block3.$block4.$block5.$block6.$block7.$block8);
	    #debug $h_ref_param, "1 field added.\n";

	}
	if ($length == 527)
	{
	push (@$opl_file,$block1.$block2.$block3.$block4.$block5.$block6.$block7);
	#debug $h_ref_param, "no field added.\n";
	}
	#print $line. "\n";
    }
    debug $h_ref_param, "File new " .$file. " length = " .$length. "\n";	
    #@$lref_content= $line;
    $$ref_data = @$lref_content[0];


    return 1;
}

# Following brace is to declare a static hash additional_cache that can only
# be seen by the following functions used for caching.
{
    my %additional_info_cache=();

    sub cached_additional_info_exist
    {
	my $l_ref_line=shift @_;
	my $res=0;
	
	my $thekey="$l_ref_line->[1]$l_ref_line->[2]$l_ref_line->[3]$l_ref_line->[12]$l_ref_line->[13]$l_ref_line->[15]$l_ref_line->[16]$l_ref_line->[22]$l_ref_line->[14]$l_ref_line->[17]";
	
	if (exists $additional_info_cache{"$thekey"})
	{
	    $res=1;
	}
	else
	{
	    $res=0;
	}
	
	return $res;
    }

    sub cached_additional_info_store
    {
	my $l_ref_line=shift @_;
	my $value=shift @_;
	
	my $thekey="$l_ref_line->[1]$l_ref_line->[2]$l_ref_line->[3]$l_ref_line->[12]$l_ref_line->[13]$l_ref_line->[15]$l_ref_line->[16]$l_ref_line->[22]$l_ref_line->[14]$l_ref_line->[17]";

	$additional_info_cache{"$thekey"}=$value;
    }

    sub cached_additional_info_get
    {
	my $l_ref_line=shift @_;
	my $thekey="$l_ref_line->[1]$l_ref_line->[2]$l_ref_line->[3]$l_ref_line->[12]$l_ref_line->[13]$l_ref_line->[15]$l_ref_line->[16]$l_ref_line->[22]$l_ref_line->[14]$l_ref_line->[17]";
	
	my $res=$additional_info_cache{"$thekey"};
	
	return $res;
    }


# End of additional_cache hash visibility.
}


################################################################################
#
################################################################################
sub get_additional_info_id
{
    my $h_ref_param=shift @_;
    my $l_ref_line=shift @_;
    my $res="";

    # First see whether we don't have the associated additional info
    # in cache already!
    if (cached_additional_info_exist($l_ref_line)!=0)
    {
	$res=cached_additional_info_get($l_ref_line);
	debug $h_ref_param, "Using cached value: $res\n";

	return $res;
    }

    # Declared here because it will used in whatever situation.
    my $get_query="SELECT id FROM imei_additional_info WHERE pth='$l_ref_line->[1]' and pts='$l_ref_line->[2]' and simcard_number='$l_ref_line->[3]' and colis_number='$l_ref_line->[12]' and customer_colis_number='$l_ref_line->[13]' and ss_colis_number='$l_ref_line->[15]' and customer_ss_colis_number='$l_ref_line->[16]' and mobile_number='$l_ref_line->[22]' and nb_imei_colis='$l_ref_line->[14]' and nb_imei_ss_colis='$l_ref_line->[17]';";

    my $count_query="SELECT COUNT(*) FROM imei_additional_info WHERE pth='$l_ref_line->[1]' and pts='$l_ref_line->[2]' and simcard_number='$l_ref_line->[3]' and colis_number='$l_ref_line->[12]' and customer_colis_number='$l_ref_line->[13]' and ss_colis_number='$l_ref_line->[15]' and customer_ss_colis_number='$l_ref_line->[16]' and mobile_number='$l_ref_line->[22]' and nb_imei_colis='$l_ref_line->[14]' and nb_imei_ss_colis='$l_ref_line->[17]';";

    my $sth = $h_ref_param->{DB}->prepare("$count_query");
    $sth->execute();
    my $ref = $sth->fetchrow_hashref();
    $sth->finish();

    if ($ref->{'COUNT(*)'}!=0)
    {
	# This additoinal info already exists. We just need to return its id.
	$sth = $h_ref_param->{DB}->prepare("$get_query");
	$sth->execute();
	$ref = $sth->fetchrow_hashref();
	$sth->finish();

	$res=$ref->{'id'};
    }
    else
    {
	# This additional info doesn't exists yet. Creating it and obtaining his id.
	my $create_query="INSERT into imei_additional_info (pth,pts,simcard_number,colis_number,customer_colis_number,ss_colis_number,customer_ss_colis_number,mobile_number,nb_imei_colis,nb_imei_ss_colis) values('$l_ref_line->[1]','$l_ref_line->[2]','$l_ref_line->[3]','$l_ref_line->[12]','$l_ref_line->[13]','$l_ref_line->[15]','$l_ref_line->[16]','$l_ref_line->[22]','$l_ref_line->[14]','$l_ref_line->[17]');";
	$sth = $h_ref_param->{DB}->prepare("$create_query");
	$sth->execute();
	$sth->finish();

	# Get the id of the newly selected record!
	$sth = $h_ref_param->{DB}->prepare("$get_query");
	$sth->execute();
	$ref = $sth->fetchrow_hashref();
	$sth->finish();

	$res=$ref->{'id'};

    }

    cached_additional_info_store($l_ref_line,$res);

    return $res;    
}

################################################################################
#
################################################################################
sub process
{
    my $h_ref_param=shift @_;
    my $err=0;
    
    my $imei="";
    my @loplcontent = ();
    my $opldata = "";
    my @lopldata = ();
    my $opl_id = "";
    my $num_opl = "";
    my $event_id = "";
    my $com_ref_id="";
    my $client_id=0;
    my @opl_file= ();
    my $new_opl_file = "";
    my $res=0;
    my $imei_additional_info_id=0;
    my @splitlines=();

    print_h_content($h_ref_param);
    
    if (! db_connect($h_ref_param))
    {
	error("Failed to connect to database.\n");
	return 3;
    }

    info "Processing file $h_ref_param->{INPUTFILE}... \n";
    
    chomp($h_ref_param->{INPUTFILE});
    @lopldata = ();
    $opl_id = 0;

    my ($name,$path,$suffix) = fileparse("$h_ref_param->{INPUTFILE}",());
    my $file_create_date="";
    my $mystat = stat($h_ref_param->{INPUTFILE});

    $file_create_date = get_date("-",localtime($mystat->mtime))." ".get_time(":",localtime($mystat->mtime));
    debug $h_ref_param, "File created date: " .$file_create_date. "\n";
    
    info "Extracting content... \n";
    if (! get_content($h_ref_param,$h_ref_param->{INPUTFILE},\@loplcontent,\$opldata,\@opl_file))
    {
	error("File $name may be wrong.\n");
	$res=7;
	goto ENDPROCESS;
    }

    #warn Dumper [@loplcontent];
    @lopldata = split(/;/, $opldata);
    
    if (db_file_already_processed($h_ref_param,substr($lopldata[7],-8,10)))
    {
	warning "File $name already uploaded. Skipping... \n";
	$res=8;
	goto ENDPROCESS;
    }

    # check whether the IMEI is already exists in db
    # if true, block the OPL
    
    # disabled
    #if (! db_imei_already_processed($h_ref_param,\@loplcontent))
    #{
	#error "OPL $name contains duplicated IMEI. \n";
	#$res=18;
	#goto ENDPROCESS;
    #}

    info "Getting COM_REF_ID... \n";
    $com_ref_id = db_get_id($h_ref_param,"SELECT count(*) FROM commercial_ref WHERE ref='$lopldata[4]';","SELECT * FROM commercial_ref WHERE ref='$lopldata[4]';");
    if ($com_ref_id == 0)
    {
	$com_ref_id=1;
	#error("COM_REF_ID not found in DB for reference $lopldata[4]. \n");
	#$res=13;
	#goto ENDPROCESS;
    }
    #print "COM_REF_ID: " .$com_ref_id. "\n";
    
  GETCLIENTID:;
    
    info "Getting CLIENT_ID... \n";
    $lopldata[9]=~s/^0+//;
    $client_id = db_get_id($h_ref_param,"SELECT count(*) FROM client WHERE code_client='$lopldata[9]';","SELECT * FROM client WHERE code_client='$lopldata[9]';");
    if ($client_id == 0)
    {
	warning("CLIENT_ID not found in DB for client number $lopldata[9]. \n");
	#$res=14;

	info " Inserting CLIENT_CODE... \n";
	if (! db_add_client_code($h_ref_param,$lopldata[9]))
	{
	    error("Couldn't insert client code $lopldata[9] from file $name into DB. \n");
	    $res=14;
	    goto ENDPROCESS;
	}
	goto GETCLIENTID;
    }
    #print "CLIENT_ID: " .$client_id. "\n";
    #warn Dumper [@lopldata];
    info "Inserting data into DB... \n";
    if (! db_data_insert($h_ref_param,\@lopldata,$file_create_date,$client_id,$com_ref_id,$name))
    {
	error("Couldn't insert data from file $name into DB. \n");
	$res=9;
	goto ENDPROCESS;
    }
    
    # the script checks the commercial reference
    # if the reference contains "TMPT", the opl number is added to the file tmn_pt in /data/samba_share/tct-huizhou/
    if ($lopldata[4] =~ /TMPT/) {
	info "Encryption required for opl $lopldata[7]. \n";
	#my $cmd = "echo $lopldata[7] >> $tmn_pt";
	#print $cmd."\n";
	`echo $lopldata[7] >> $tmn_pt`;
    }

    info "Getting OPL_ID... \n";
    $num_opl=substr($lopldata[7],-8,10);
    $opl_id = db_get_id($h_ref_param,"SELECT count(*) FROM opl WHERE num_opl='$num_opl';","SELECT id FROM opl WHERE num_opl='$num_opl';");
    if ($opl_id == 0)
    {
	error("OPL_ID equal 0 Couldn't insert imei from file $name into DB. \n");
	$res=10;
	goto ENDPROCESS;
    }
    #print "OPL_ID: " .$opl_id. "\n";

    ## add to table "log" information about the OPL
    ## opl id -> link to table "opl"
    ## file type -> IMEI_OPL or SIMLOCK_OPL
    ## status -> new, processing, done...
    ## log -> result of the processing
    info "Inserting OPL info to log... \n";
    if (! db_log_insert($h_ref_param,$opl_id))
    {
	error("Couldn't insert info into table log from file $name. \n");
	$res=30;
	goto ENDPROCESS;
    }    

    info "Adding Event... \n";
    if (! db_add_event($h_ref_param,$opl_id))
    {
	error("Couldn't insert event from file $name into DB. \n");
	$res=15;
	goto ENDPROCESS;
    }

    info "Getting EVENT_ID... \n";
    $event_id = db_get_id($h_ref_param,"SELECT count(*) FROM event WHERE data='$opl_id';","SELECT * FROM event WHERE data='$opl_id';");
    if ($event_id == 0)
    {
	error("EVENT_ID not found in DB from OPL_ID $opl_id. \n");
	$res=16;
	goto ENDPROCESS;
    }
    #print "EVENT_ID: " . $event_id. "\n";

    # Inserting each IMEI found in the OPL into the IMEI tables by connecting 
    # the current OPL with each IMEI.
    foreach my $lines (@loplcontent)
    {
	@splitlines = split(/;/,$lines);
	
	#print $lines. "\n";
	#warn Dumper [@loplcontent];	
	$imei_additional_info_id=get_additional_info_id($h_ref_param,\@splitlines);

	debug $h_ref_param, "Inserting IMEI into DB... \n";
	if (! db_imei_insert($h_ref_param,$splitlines[0],$event_id,$imei_additional_info_id))
	{
	    error("Couldn't insert imei $splitlines[0] from file $name into DB. \n");
	    $res=11;
	    goto ENDPROCESS;
	}
    } # foreach my $lines (@loplcontent)

    $new_opl_file = "/tmp/" .$name. ".tmp";
    
    open (FILE,">$new_opl_file");
    
    foreach my $line (@opl_file)
    {
	print FILE $line. "\n";
	
    }
    close (FILE);

  ENDPROCESS:;
    
    info "End of Process. \n";
    
    if (! db_disconnect($h_ref_param))
    {
	error("Failed to disconnect from database.\n");
    }
    
    return $res;
}

################################################################################
#
################################################################################
sub print_version
{
  my $h_ref_param=shift @_;
  print $h_ref_param->{EXECUTABLE_NAME}." ".$h_ref_param->{VERSION}." ".$h_ref_param->{RELEASE_DATE}."\n";
}

################################################################################
#
################################################################################
sub parse_args
{
    my $h_ref_param=shift @_;
    
    my $arg="";
    my $err=0;
    
    while ($arg=shift @ARGV)
    {
	chomp($arg);
	if ( $arg =~ /^-/ )
	{
	    if ( ($arg =~ /^-h/) || ($arg =~ /^--help/))
	    {
		print_usage($h_ref_param);
		exit(0);
	    }
	    elsif ( ($arg =~ /^-d/) || ($arg =~ /^--debug/))
	    {
		$h_ref_param->{DEBUG}=1;
	    }
	    elsif ( ($arg =~ /^-v/) || ($arg =~ /^--version/))
	    {
		print_version();
		exit(0);
	    }
	    
	    elsif ( ($arg =~ /^-l/) || ($arg =~ /^--log/))
	    {
		$arg=shift @ARGV;
		$h_ref_param->{LOG}=$arg;
	    }

	    else
	    {
		error "Unexpected option >$arg<.\n";
		$err++;
		next;
	    } 
	}
	else {
	    #<opl type>: Mandatory. type of OPL. NS (not simlocked) or S (simlocked)\n";
	    if (! exists $h_ref_param->{OPL_TYPE})
	    {
		$h_ref_param->{OPL_TYPE}=$arg;
	    }
	    #<center>: Mandatory. ID of the center\n";
	    elsif (! exists $h_ref_param->{CENTER})
	    {
		$h_ref_param->{CENTER}=$arg;
	    }
	     #<inputfile>: Mandatory. Input File\n";
	    elsif (! exists $h_ref_param->{INPUTFILE})
	    {
		$h_ref_param->{INPUTFILE}=$arg;
	    }
	    #<dbname>: Mandatory. Local database name to use <dbname> database.\n";
	    elsif (! exists $h_ref_param->{DBNAME})
	    {
		$h_ref_param->{DBNAME}=$arg;
	    }
	    #<dblogin>: Mandatory. Local database login to use <dbname> database.\n";
	    elsif (! exists $h_ref_param->{DBLOGIN})
	    {
		$h_ref_param->{DBLOGIN}=$arg;
	    }
	    #<dbpasswd>: Mandatory. Local database password to use <dbname> database.\n";
	    elsif (! exists $h_ref_param->{DBPASSWD})
	    {
		$h_ref_param->{DBPASSWD}=$arg;
	    }
	}
    }
    
    if (! exists $h_ref_param->{DBPASSWD})
    {
	error("Missing arguments.\n");
	$err++;
    }
    
    if ($err>0)
    {
	error "Aborting due to previous error(s). Use -h or --help for usage.\n";
	return 1;
    }
    
    return 0;
}
    
################################################################################
#
################################################################################
sub print_usage
  {
    my $h_ref_param=shift @_;

    print "USAGE: ".$h_ref_param->{EXECUTABLE_NAME}." [-h|--help] [-d|--debug] [-v|--version] [-l|--log <log file>] ...] <opl type> <center> <inputfile> <dbname> <dblogin> <dbpasswd>\n";
    print "\n";
    print "PURPOSE:\n";
    print "   Insert OPL file data into the database \n";
    print "\n";
    print "PARAMETERS DESCRIPTION:\n";
    print "\n";
    print "   <opl type>: Mandatory. type of OPL. S->simlocked, NS->not simlocked\n";
    print "\n";
    print "   <center>: Mandatory. ID of the center. 1->UNKNOWN, 2->tct-huizhou, 3->global\n";
    print "\n";
    print "   <inputfile>: Mandatory. input file.\n";
    print "\n";
    print "   <dbname>: Mandatory. Local database name to use <dbname> database.\n";
    print "\n";
    print "   <dblogin>: Mandatory. Local database login to use <dbname> database.\n";
    print "\n";
    print "   <dbpasswd>: Mandatory. Local database password to use <dbname> database.\n";
    print "\n";
    print "OPTIONS DESCRIPTION:\n";
    print "   [-h|--help]:\n";
    print "      Print this usage message and exit.\n";
    print "\n";
    print "   [-v|--version]:\n";
    print "      Print the version number and exit.\n";
    print "\n";
    print "   [-d|--debug]:\n";
    print "      Print excessive output to understand the processing.\n";
    print "\n";
    print "\n";
  }

################################################################################
#
################################################################################
sub open_log
{
    my $h_ref_param=shift @_;

    if (! open($G_LOG,">>".$h_ref_param->{LOG}))
    {
	warning "Cannot create log file ".$h_ref_param->{LOG}." in current directory.\n";

	$h_ref_param->{LOG}="/tmp/".$h_ref_param->{EXECUTABLE_NAME}.".log";

	if (! open($G_LOG,">".$h_ref_param->{LOG}))
	{
	    warning "Still cannot create alternative log file ".$h_ref_param->{LOG}.". No log will be performed.\n";
	    $G_LOG=0;
	}
    }
}


################################################################################
#
################################################################################
sub intro
  {
    my $h_ref_param=shift @_;
    my $err=0;
    my $date_for_file="";

    # Updating some global variables.

    $h_ref_param->{START_TIME}=get_date("-")." ".get_time(":");

    $date_for_file=get_date("-")."_".get_time("-");
    
    $h_ref_param->{VERSION}="0.9.13";
    $h_ref_param->{EXECUTABLE_NAME}="import_opl.pl";

    $h_ref_param->{LOG}=$h_ref_param->{EXECUTABLE_NAME}.".$date_for_file.log";

    $h_ref_param->{LOCK_FILE}="/tmp/".$h_ref_param->{EXECUTABLE_NAME}.".lock";
    
    $h_ref_param->{RELEASE_DATE}="2007/10/12";

    # Following instruction forces flushing for each output to STDOUT or STDERR.
    $|=1;

    return 0;
  }

################################################################################
#
################################################################################
sub outtro
{
    my $h_ref_param=shift @_;
    if ($G_LOG)
    {
	close($G_LOG);
    }

    return 0;
}

################################################################################
#
################################################################################
sub main
{
  # This hash list will be used to store input parameters and some other
  # usefull information.
  # The actual valable keys are:
  #   VERSION:           string: current version of the tool.
  #   START_TIME:        string: start time of the script in the form YY_MM_DD.HH.MM.SS
  #   EXECUTABLE_NAME:   string: name of the current script.
  #   LOG:               string: name of the log file.
  #   RELEASE_DATE:      string: date of current tool release in form: YYYY/MM/DD
  #   DEBUG:             boolean: if this key exists, then the current script is run in
  #                               debug mode.
  #
  my %params=();
  my $res;
  
  if (intro(\%params)!=0)
  {
      error "Error while executing intro... Aborting.\n";
      exit(1);
  }

  if (parse_args(\%params)!=0)
  {
      error "Error while parsing arguments... Aborting.\n";
      exit(2);
  }
  
  open_log(\%params);
  
  $res = process(\%params);
  #if ($res!=0)
  #{
      #error "Error while processing... Aborting.\n";
  #}

  if (outtro(\%params)!=0)
  {
      error "Error while executing outtro... Aborting.\n";
      exit(12);
  }

  exit($res);

}


################################################################################
#
################################################################################

main();
exit(0);

